package com.jt.dao;

import com.jt.pojo.User;
import com.jt.util.JDBCUtil;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
public class UserDAOImpl implements UserDAO{

    @Override
    public int insert(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "insert into stu(name,username,password) values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            row = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,null,ps);
        }
        return row;
    }

    @Override
    public int delete(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "delete from stu where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            row = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,null,ps);
        }
        return row;
    }

    @Override
    public int update(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        int row = 0;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "update stu set name=?,username=?,password=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getName());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setInt(4,user.getId());
            row = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,null,ps);
        }
        return row;
    }

    @Override
    public List<User> findAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        List<User> userList = new ArrayList<>();
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select * from stu";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                /*int id = rs.getInt("id");
                String name = rs.getString("name");
                String username = rs.getString("username");
                String password = rs.getString("password");
                User user = new User(id,name,username,password);*/
                User user = getUser(rs);
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,rs,ps);
        }
        return userList;
    }

    @Override
    public User findUserById(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select * from stu where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                /*id = rs.getInt("id");
                String name = rs.getString("name");
                String username = rs.getString("username");
                String password = rs.getString("password");
                user = new User(id,name,username,password);*/
                user = getUser(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,rs,ps);
        }
        return user;
    }

    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = "select * from stu where username=? and password=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            rs = ps.executeQuery();
            if(rs.next()){
//                int id = rs.getInt("id");
//                String name = rs.getString("name");
//                username = rs.getString("username");
//                password = rs.getString("password");
//                user = new User(id,name,username,password);
                user = getUser(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(conn,rs,ps);
        }
        return user;
    }


    public User getUser(ResultSet rs) throws SQLException {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String username = rs.getString("username");
        String password = rs.getString("password");
        User user = new User(id,name,username,password);
        return user;
    }
}
